Introduction

Inventory Manager is designed to aid in three areas: Inventory Control, Chemical Inventory and the annual Physical Inventory. It is based on an inventory control system that was first developed in 2003. Prior to developing the inventory control system for printing, the annual inventory was always chaotic and time-consuming; a nightmare. Now one person can inventory our approximately 450 items in a single day, and it takes an hour or two to record them and print an extended inventory. With four people counting, we could finish before lunch.

Inventory Manager is not a separate program; it is a Microsoft Access database. The Inventory Manager distribution can be acquired from Dana Bell or downloaded from the UTHCT Printing and Copy Services sharepoint site, http://sharepoint/sites/printandcopy/. Simply create a folder and extract the files into it. Alternately, it may be available in http://www.saltatoria.com/dbell/files/. For non-XP users, a zip file installer can be downloaded at http://www.nirsoft.net/utils/zipinst.html.

Before you setup Inventory Manager you may want to see what it is like. The distributed version includes sample data so you can see how the system looks and works. Navigate through the menus to see what the forms and reports look like. Go through the steps for ordering and physical inventory to make sure the system has all you need and prepare you for what you need to do.

Inventory Manager is a relational database that includes multiple tables of information that is inter-related. It takes longer to setup, setting up the base tables and other configuration. In use, it is much easier to maintain with things like drop down lists. Relational databases also provide better control over data integrity.

Related Topics:

Purpose

Limitations

Support

History

How it works

Menus


Purpose

Inventory Manager was developed primarily to assist the departments for which we do printing, to better track their printing inventory. At this time of year, however, it may be most valuable for doing your physical inventory. Inventory Manager simplifies the annual inventory process by using your inventory records from previous years, and allowing you to just update the counts each year.
With entry forms, navigating the inventory is considerably easier than working with a cumbersome spreadsheet, and it offers more options for sorting, filtering and creating reports; all from the same data.
Having that inventory information on computer can also help in two other areas, Chemical Inventory Records and Inventory Control.


Limitations

The printing database from which this was extracted also included ordering functionality as well as perpetual inventory control. That is, I used it to also keep track of usage and orders, so that the book inventory was always up to date (or perpetual). Because those features are best designed to work with individual department needs and structure, and to make this application simpler to use, they were not included.
Inventory Manager is currently a local database only. It is not networked. If you are interested in networking or integrating with existing systems, you will need to contact Information Technology.


Support

Inventory Manager was developed independent of IT, so any questions about the Inventory Manager database itself may need to be directed to Dana Bell at dana.bell@uthct.edu.
As time permits, I will be happy to help you get started. If you will let me know if you are using Inventory Manager I can also contact you about further developments. Questions about integrating Access with other software or networking, however, will need to go to IT.


History

For several years now we have been using an Access database to assist in our physical inventory, among other things related to managing the printing pressroom and related areas. Some elements of that system were also modified for use in the Copy Center and for other functions.
In this latest evolution, that database has been stripped down and cleaned up to act as the starting point for Inventory Manager. As a part of the clean-up, this database now uses the Leszynski object naming convention for basic objects and was re-designed to be in Boyce-Codd normalized form. In short, that means distinctive repetitive data (i.e. Locations) are in separate tables. The evil lookup fields in tables used in the previous system have also been eliminated.

and Future

While Inventory Manager doesn't have a definite future, it would be cool if it could be used to keep records of your orders for inventory control. Actually that is in the works. It would also be nice to use the statistics to determine Reorder Level and Order Quantity. It would be neat if it were integrated with other applications, so that it could pull in the item or pricing information automatically. And, it would be awesome if you could use this database to actually order stuff for your department from your list of items. But that's in the future and determining the best way to implement some of these things will depend on a lot of research and feedback from users.


How it works

Inventory Manager is an inventory database with multiple applications. All entries in the database are used for Physical Inventory. For Chemical Inventory, a subset of the data is used (All items checked as MSDS Items), and additional fields are added for the chemical characteristics of those items. For Inventory Control, another subset is used (All items checked as Stock Item) and a couple more fields used (optionally).


Menus

All necessary forms and reports are linked to a Switchboard menu system. The menus provides a simple system for navigating the elements of the database. The Main Menu lists the primary areas in which you may use Inventory Manager. The submenus allow you to navigate within each area.

In most cases, the items are listed in the order that you would use them. For example, in Physical Inventory you start with the count sheets at the top of the menu, and end with the Extended Inventory Report.

There is also a custom menubar for Inventory Manager that provides access to the options.

Note: All reports are displayed in Print Preview mode from which you can then print using the Access menu. Click on the preview to zoom in or out.


Getting Started

Preparation starts before you start using the computer. Physically organize your inventory and identify the areas you have to inventory before you try to organize the data. If you want to give Inventory Manager a try before time for inventory (recommended), begin with a single area, or a few items to see how it works. If you want to get started quickly, you can follow the Quick Start guide at the end for Physical Inventory Essentials.

Related Topics

Setup

Edit Inventory Items


Setup

Before you use this database you will need first identify your department and edit some tables. From the Main Menu, select the Setup option. This will give you options to configure your department specifications, locations, vendors and inventory classifications.

Department

You will want to first identify the database for your department. You enter your department on the Department screen, along with the Department manager and Inventory Contact.
If you will be doing inventory for multiple departments then you should make several copies of the database before you begin. You must exit Access and use Windows Explorer to do this. You can use alternate filenames, or create separate folders for each, depending on your preference. The opening screen will identify the department for which the database is configured.

Tables

The first steps to setting up the database is to update the supporting tables, particularly the Locations table. You will do your inventory by Location and most of the reports are sorted by Location.
Edit the tables to reflect your information in Locations, Vendors, and Classifications. These tables are used for looking up commonly used specifications. These are the options that will be presented when you select the associated drop down list in a form. It is not necessary to delete unused table data, since these items are only included if you select them from a drop-down list.

  • Sites includes the organizational units, or campuses. This is useful if you maintain records for multiple sites. If your inventory is for a single location, only one site is needed.
  • Buildings includes the names (or numbers) of your physical building.
  • Locations are the areas within your department where inventory items are located. Each location should have a site and building.
  • Departments are the names of all departments for which the inventory is maintained.
  • Vendors are the companies that you acquire items from.
  • Classifications are ways in which you can further classify items.

Inventory items will be linked to the selected entries in these tables.
You may want to include an entry in each table named Unlisted, and use that for items for which you don’t have an entry in the list. After the items are entered, add the missing location or whatever, and then come back to edit those entries previously Unlisted.

Note: Changes will be reflected in any linked items. If you list an item as in the Office and then change the Location entry to Storeroom, every item that was listed as in the Office will now be listed as in the Storeroom.


Edit Inventory Items

The simplest way, and the best preparation for Physical Inventory, is to enter the information prior to Physical Inventory. You may, however, want to wait until after inventory to enter all of the items. If you want to do that, you can simply print out blank inventory sheets for that.

Sample Data

The first step is to edit or delete all sample data. Start by opening the Edit Inventory and delete items you don’t need listed. Several items from Stores, Printing and the Copy Center are included as sample, although you may be using some of these same items.
Tip: One trick to quick deletions is to switch to Datasheet View (View > Datasheet View) and delete records from the database by deleting rows from the table. Switch back to the form with View > Form View.

New Inventory

Enter new items. Enter Inventory (before or after physical inventory)
It is important that you include as much information as possible. Don’t overlook the prices, these will be needed when you do physical inventory.

You may only want to include the description for each item. If you have multiple sizes, weights or colors of an item, there are fields available for that. The entry form is divided into tabs. When entering information, you can select other tabs, and you can use the All tab to list all characteristics, though that may be overwhelming at first. To keep from being too distracted, I suggest you only complete the General tab for each item first. Complete the information for Inventory Control and Chemicals only after you’ve done the research.

Note: Be sure to check the appropriate boxes for MSDS Item and Stock Item if you intend to use this for Chemical Inventory or Inventory Control. Both check boxes are on the General Tab.

Tip: One system for updating prices would be print out a set of count sheets and go through (latest to earliest) the requisitions or purchase orders. Once the item has a price (latest) you can skip that item on any previous requisition or purchase order.

On Printing

If you are required to inventory printing prices, you should not use a unit price. Print unit pricing varies significantly based on the quantity. Instead, find the latest price and quantity and enter these values in Price and "PricePer", respectively.

On Item Numbers

The forms include several numbers for the way products are identified. The UTHCT numbers are numbers given by some UTHCT department. The numbers for stock forms, for example, are UTHCT stock numbers.
There are also Vendor Stock Numbers. These are the numbers that might be used to order an item form a vendor. The Product number listed in the Chemical screen refers to a number that appears on the selected item, or the manufacturer’s number. This may not be the number that a vendor uses.

Importing

If you already have your inventory in a spreadsheet or other format, it can be imported/converted to Microsoft Access. For a simple copy/paste of the Description field, it would be helpful to use Datasheet view in the Edit Inventory form. The best way to do this is format the spreadsheet columns to match that of Inventory Manager and copy and paste as you would from one spreadsheet to another.
In some cases, Access can directly edit data in the host application, although it is not advisable. If you have a need to do this, relationships have to be re-established and data may need to be modified to maintain referential integrity in the database.


Physical Inventory

This part of the database may be the most useful, although you use it only once a year. This simplifies inventory by listing all items, sorted by Location, and all you have to do is enter the quantity and add any new items.

Note: Inventory should be done by location, that is, look at the item on the shelf and then find it on the list. Afterward, you may check the list for any items for which a count was not recorded. It may have been moved to a new location.

To save time, the inventory items should already be entered before you begin a physical count. If you really want to start this database with an annual inventory, the steps would be different. See Annual Inventory Deployment.

Related Topics

Set Year

Inventory Count Sheets

Record Inventory

Add/Edit Inventory Items

Extended Inventory Report


Set Year

This determines the headings of physical inventory reports.


Inventory Count Sheets

Select this to print count sheets for all items in inventory. The count sheets also include an amount called Book Inv. This is the current inventory in the database. You can check the box next to it if there is no change since the last inventory.

Blank Count Sheets

Blank count sheets are simply forms for entering new inventory items. When printing count sheets, you may need to print one blank sheet for each area.
If you plan to enter the initial data into Inventory Manager based on this year’s physical inventory, you would use these sheets.


Record Inventory

When you record inventory, you will also notice that items are listed in the same order as on the count sheets. The fields across are also in the same order.
There are two fields that you cannot change from this form. The Description is compiled from all the characteristics (Size, Color…) that make up the description. Also, the Extended amount is a calculated field that is based on the price, count and “price per” fields.

As you record the counts on this form, you will also notice that tabbing will go from record to record in the count field. This is designed to speed data entry. In order to edit another field, you will need to select it with your mouse.
There is a button that you can use to edit that particular item. This will display the current record for editing. Exit the screen to return to Recording Inventory.


Add/Edit Inventory Items

In order to add items to the database, or to do extensive editing, select Add/Edit Inventory from the menu. This is the same form that is used to enter initial inventory.
There is a Find field at the top of the form that allows you to find a specific item for editing. Click the arrow at the right edge of the box, scroll and click on the item to edit.

See Edit Inventory.


Extended Inventory Report

The Extended Inventory Report in the report to submit to Accounting once you have verified that everything is correct. This option displays the report in print preview mode. Click on the preview to zoom in or out. When you are ready to print, select Print… from the File menu.

Condensed

When the extended inventory is report is created, it is condensed. Only those items for which there is inventory in stock will be listed. There will be subtotals for each location, and a trailing page with the grand total.

Electronic Version

File > Export… and select the Excel format.

To pretty it up, you may need to delete the first column or two. Then create a header with your department information, adjust the column widths, format the price and extension columns as currency, and give the file a new name, i.e. FY07Inventory. In the future, Health Center policy may dictate the file naming convention to be used.


Inventory Control

The primary purposes of inventory control are to minimize inventory, minimize stock outs and thus minimize costs. This database simply makes it easier to do periodic physical inventory checks.

Related Topics

Edit Inventory Control

Inventory Checklist


Edit Inventory Control

From the Inventory Control menu, select Edit Inventory Control to edit the inventory control properties of each item. This is the second tab on the same form you used to initially enter the item. Filling in the forms is easy. Knowing what to put in the fields may be more difficult. See the section on Principles of Inventory Control for more information on that.

Stock Item

If you haven’t already, you will need to tag every item that you order regularly as a stock item. The Inventory Checklist will only include stock items. If you have already checked Stock Item for all of the items you order regularly, you can filter the list by pressing the button Stock Items Only.
Notes: This filter applies to all tabs in the open form. When the form is closed, the filter is closed so the next time the form opens it will again display all items.

Reorder Level and Order Quantity

On those items, you then need to enter amounts for Reorder Level and Order Quantity. This will be printed on the Inventory Checklist as a reminder to you.
Of course, if you know when and how much to order, these are not essential. You may also choose to order a quantity to raise your inventory to a certain stock level.
You can print a checklist (see below) and use that as a worksheet as you decide on the best inventory levels.

Vendor

This will help you remember where you ordered an item.


Inventory Checklist

The Inventory Checklist is a report listing items in inventory that are tagged as Stock Items. This list is handy for several reasons. With a list you don’t have to manually write a description. Secondly, the reorder information is on the list for your reference. Finally, a list will keep you from overlooking an item for which you are completely out.

The report is sorted by Location and then by descriptive characteristics of each item. When reviewing your inventory, simply check items that need to be ordered, and write the quantity in the space. When an order is placed, you can then indicate any details (date, Req, supplier) you want about the order. In particular, as you order supplies from the checklist, make a note of any changes in price so they can be updated in the database.

The report is displayed as a Print Preview. Click on the preview to zoom in or out. In order to print it, select the Print… option from the toolbar.

Tips: An easy way to remind yourself to do periodic inventory checks is to use Outlook to set a recurring appointment.


Chemical Inventory

Just as in other aspects, you may need to do some research before you know what to put in for Chemical Inventory. If you’ve prepared this in the past, then that information should be available.

Related Topics

Edit Chemical Properties

Departmental Chemical Inventory


Edit Chemical Properties

This option opens the Inventory Editor to the Chemical Properties tab. You will only need to enter this information for MSDS Items. If you have already checked MSDS for all items in your chemical inventory, you can filter the list by pressing the button MSDS Items Only.

Notes: This filter applies to all tabs in the open form. When the form is closed, the filter is closed so the next time the form opens it will again display all items.

Enter the relevant information. If you have an MSDS book with each sheet numbered, enter that number as the MSDS Number. If there is a number in this field then the report will indicate that you “Have MSDS”.

For class and state, you simply select the proper item for the respective drop down list.

For Container Size you can enter any description, which may not be the same as the Unit of Measure for ordering purposes or physical inventory. Similarly, the Quantity on Hand refers to the number of containers, or to a more detailed description if you prefer.


Department Chemical Inventory

This creates a report listed all chemicals and their characteristics. The report is displayed as a Print Preview. Click on the preview to zoom in or out. In order to print it, select the Print… option from the toolbar.

While the report that Inventory Manager creates is similar to the one that Safety needs, it isn’t in spreadsheet form. In order to put the data onto a spreadsheet, export the report to a spreadsheet file and then copy and paste the data into the Excel spreadsheet file that Safety provides. Of course, verify that the contents match up. It’s possible that Safety will add, delete or move a column.


Chemical Tables

Note: The Chemical Tables are based on the properties used in Safety. You should only change these if there are changes in Safety department specifications. Classifications are normally listed at the bottom of the Chemical Inventory spreadsheet distributed by Safety each year.


Capital Inventory

New in Inventory Manager 1.3 is the ability to keep records of your capital inventory. The basic options allow you to maintain records for your department, including purchase and disposal records.

As is true for any new database, you may need to do some research before you can complete the forms for each item. Capital Inventory includes fields for all specifications requested by Accounting, as well as other information on each inventory item. You can start by adding the items from your latest Inventory List spreadsheet.

With Multi-site options you can maintain records for multiple departments at multiple sites. In order to do this you will have to setup information in the tables for sites, buildings, departments.

Related Topics

Edit Capital Inventory

Capital Inventory Report

Accounting Datasheet

Multi-site


Edit Capital Inventory

Capital Inventory is entered on a tabbed form much like the Supply Inventory. There is a tab for General, Purchase and Disposal. You may omit any information that you do not need.

General

The General tab includes basic information to identify the inventory item and its location. There is an optional field for Custodian. If not used, the Custodian is identified as the location on reports. In the multi-site version you can use the Department field to indicate the location of an item, organizationally.

Purchase

Purchase Information is entered in the options available from the Purchase tab. This includes Date purchased, price, initial location, vendor and details of the purchase.

Disposal

Information about how an item is disposed of is under the Disposal tab. You may include such information like, sold, donated, or transferred to another department.

Sample Data

The distributed version of the database may include some sample data. You can delete items from the database by deleting each item with the delete button. Alternately, you can select View > Datasheet View, and then Edit > Select All Records, and then press the delete key. You can then select View > Form View to enter new items or close this form if you will not be entering any items.

Notes

Note: Custodian is the name of the person that is is possession of the item. If not provided, then room number is used for Custodian on the Capital Inventory Report.

Note: There are two ways to identify where an item is located.
Named Locations are used to describe where inventory is physically located. Any general or specific location can be used to describe a location, so you can identify it. Office, Accounting, Greg's Office, and Room 113 are all acceptable. In general you would want to use descriptive names that don't include room numbers. The characteristics of the location include Site and Building from the table lists, and Room number.
Departments identify the organizational location of an item.
Inventory Manager distinguishes between physical and organizational. Future administrative policy may be used to identify where and how a capital inventory item is recorded.


Capital Inventory Report

The Capital Inventory Report produces a report of all items in inventory, grouped by Location. This report contains Custodian, Property Number, Item Description and Serial Number. This can be used to review your capital inventory items.


Capital Inventory Accounting Datasheet

The Accounting Datasheet is an inventory report in the same format as past spreadsheets distributed by the Accounting department when reviewing the capital inventory at the Health Center. This can be used to compare your department items with the published spreadsheet and submit necessary changes to Accounting.


Multi-site Capital Inventory

Capital Inventory for multiple sites can be monitored with this database. Multi-site operations identify inventory by site (or campus) and by department.

The folllowing additional information is required to track capital inventory on multiple sites. You will need to identify sites and departments in the Setup tables. From the Main Menu, select Setup and then Edit Tables.

The Capital Inventory form includes drop down menus to select a Department on the General tab and on the Purchase tab.

An additional tab is included to record movement of capital inventory to different locations and across departments.


Leftovers

The following are footnotes, or appendices, on other aspects of Inventory Manager that you may want to consider.

Related Topics

Optional Reports

Copies and Backups

Quick Start

Principles of Inventory Control

Annual Inventory Deployment

Expanded use

Snapshots

Why use a database


Optional Reports

In the main menu there is an additional option, Optional Reports. This is the desktop drawer of Inventory Manager miscellany. This will include items that have not been incorporated into the menu system, the documentation or are optional for regular use of Inventory Manager.

At this writing, this includes the reports, Checklist By Vendor and a Condensed Checklist.


Copies and Backups

Inventory Manager has a custom local backup function in the Setup menu. When selected, Inventory Manager backs up the database with a filename with the date and time appended.

If you use this database from year to year, you may also want to save a copy after each physical inventory. In order to save a copy of the database on your local computer under a different name, you will need to exit and use Windows Explorer to make the copy. Use the Windows-E (hold the Windows key and press E) to bring up Windows Explorer.

I suggest you use a system that identifies the year, like FY07Inventory. If your system displays extensions for files, you may need to explicitly add .mdb to the end for any copies you make.

These are local backups and are not a substitute for off-line (disc or CD-ROM) or external (network drive) backups. If your computer fails, any local backups may not be accessible.

You can use this procedure to make copies for separate inventory databases or for other departments, although it may be best to make copies of the original distribution.

Note: Microsoft Access does not have a Save As… option for saving the entire database file. The File > Save As… in Access is used to save objects within the database.


Quick Start (Physical Inventory Essentials)

Setup

  • Edit Locations
  • Delete Sample Data and Enter New Inventory Items

Each year…

  • Print Count Sheets
  • Count Inventory
  • Record Quantities and New Items
  • Print Extended Report
  • Backup (according to taste)


Principles of Inventory Control

For an explanation of how to get started with inventory control, see the help on our sharepoint site at http://sharepoint/sites/printandcopy/Helps/Inventory.aspx.


Annual Inventory Deployment

If you don’t have the time to prepare this database prior to inventory, you can use it to finish inventory and prepare it for next year.

Start with blank inventory sheets, do the counting and then record all the information (including the count) in the Add/Edit Inventory stage. Print the count sheets to verify if desired, and then the extended report.


Expanded use

Inventory Manager is an unprotected database, so you can create other tables, queries, forms or reports to suit your needs. This is done from the database window. You may need a different kind of report using the same data; a list of all items from a certain vendor, a list all items that are highly flammable. Access offers many ways to search, sort, or filter results. Be sure to make and keep backups, though.

Warning: Be careful about making changes to existing tables and queries. Changes to existing objects could affect the operation of other functions in Inventory Manager. Queries are used by many different reports and forms. If you need a special query, create a new one, or make a copy of an existing one and edit that.

If you must add fields to a table, add them at the end, and document them.


Snapshots

If you want to send a report from Access, there is also the option to send a Snapshot of a report. This can be done in Access with File > Send to… > Attachment and then select to send a Snapshot. A snapshot can be viewed but not edited.


Why use a database

Many offices put all information in a spreadsheet, and create new spreadsheets for every kind of report. A database stores all of the information in raw format, and then generates new reports from that.

With a database, you can input data in various forms, and output different reports for different purposes. Just in this basic database, you can create two reports for physical inventory, one for inventory control, and one for your chemical Inventory. The individual inventory items only have to be entered or changed once.


Inventory Manager

Inventory Manager

Introduction

Getting Started

Physical Inventory

Inventory Control

Chemical Inventory

Capital Inventory

Leftovers